pandas is a Python package providing fast, flexible, and expressive data structures designed to work with relational or labeled data both. It is a fundamental high-level building block for doing practical, real world data analysis in Python.
pandas is well suited for:
Key features:
from IPython.core.display import HTML
HTML("<iframe src=http://pandas.pydata.org width=800 height=350></iframe>")
%matplotlib inline
import pandas as pd
import numpy as np
# Set some Pandas options
pd.set_option('html', False)
pd.set_option('max_columns', 30)
pd.set_option('max_rows', 20)
A Series is a single vector of data (like a NumPy array) with an index that labels each element in the vector.
counts = pd.Series([632, 1638, 569, 115])
counts
0 632 1 1638 2 569 3 115 dtype: int64
If an index is not specified, a default sequence of integers is assigned as the index. A NumPy array comprises the values of the Series
, while the index is a pandas Index
object.
counts.values
array([ 632, 1638, 569, 115])
counts.index
Int64Index([0, 1, 2, 3], dtype='int64')
We can assign meaningful labels to the index, if they are available:
bacteria = pd.Series([632, 1638, 569, 115],
index=['Firmicutes', 'Proteobacteria', 'Actinobacteria', 'Bacteroidetes'])
bacteria
Firmicutes 632 Proteobacteria 1638 Actinobacteria 569 Bacteroidetes 115 dtype: int64
These labels can be used to refer to the values in the Series
.
bacteria['Actinobacteria']
569
bacteria[[name.endswith('bacteria') for name in bacteria.index]]
Proteobacteria 1638 Actinobacteria 569 dtype: int64
[name.endswith('bacteria') for name in bacteria.index]
[False, True, True, False]
Notice that the indexing operation preserved the association between the values and the corresponding indices.
We can still use positional indexing if we wish.
bacteria[0]
632
We can give both the array of values and the index meaningful labels themselves:
bacteria.name = 'counts'
bacteria.index.name = 'phylum'
bacteria
phylum Firmicutes 632 Proteobacteria 1638 Actinobacteria 569 Bacteroidetes 115 Name: counts, dtype: int64
NumPy's math functions and other operations can be applied to Series without losing the data structure.
np.log(bacteria)
phylum Firmicutes 6.448889 Proteobacteria 7.401231 Actinobacteria 6.343880 Bacteroidetes 4.744932 Name: counts, dtype: float64
We can also filter according to the values in the Series
:
bacteria[bacteria>1000]
phylum Proteobacteria 1638 Name: counts, dtype: int64
A Series
can be thought of as an ordered key-value store. In fact, we can create one from a dict
:
bacteria_dict = {'Firmicutes': 632, 'Proteobacteria': 1638, 'Actinobacteria': 569, 'Bacteroidetes': 115}
pd.Series(bacteria_dict)
Actinobacteria 569 Bacteroidetes 115 Firmicutes 632 Proteobacteria 1638 dtype: int64
Notice that the Series
is created in key-sorted order.
If we pass a custom index to Series
, it will select the corresponding values from the dict, and treat indices without corrsponding values as missing. Pandas uses the NaN
(not a number) type for missing values.
bacteria2 = pd.Series(bacteria_dict, index=['Cyanobacteria','Firmicutes','Proteobacteria','Actinobacteria'])
bacteria2
Cyanobacteria NaN Firmicutes 632 Proteobacteria 1638 Actinobacteria 569 dtype: float64
bacteria2.isnull()
Cyanobacteria True Firmicutes False Proteobacteria False Actinobacteria False dtype: bool
Critically, the labels are used to align data when used in operations with other Series objects:
bacteria + bacteria2
Actinobacteria 1138 Bacteroidetes NaN Cyanobacteria NaN Firmicutes 1264 Proteobacteria 3276 dtype: float64
Contrast this with NumPy arrays, where arrays of the same length will combine values element-wise; adding Series combined values with the same label in the resulting series. Notice also that the missing values were propogated by addition.
Inevitably, we want to be able to store, view and manipulate data that is multivariate, where for every index there are multiple fields or columns of data, often of varying data type.
A DataFrame
is a tabular data structure, encapsulating multiple series like columns in a spreadsheet. Data are stored internally as a 2-dimensional object, but the DataFrame
allows us to represent and manipulate higher-dimensional data.
data = pd.DataFrame({'value':[632, 1638, 569, 115, 433, 1130, 754, 555],
'patient':[1, 1, 1, 1, 2, 2, 2, 2],
'phylum':['Firmicutes', 'Proteobacteria', 'Actinobacteria',
'Bacteroidetes', 'Firmicutes', 'Proteobacteria', 'Actinobacteria', 'Bacteroidetes']})
data
patient phylum value 0 1 Firmicutes 632 1 1 Proteobacteria 1638 2 1 Actinobacteria 569 3 1 Bacteroidetes 115 4 2 Firmicutes 433 5 2 Proteobacteria 1130 6 2 Actinobacteria 754 7 2 Bacteroidetes 555
Notice the DataFrame
is sorted by column name. We can change the order by indexing them in the order we desire:
data[['phylum','value','patient']]
phylum value patient 0 Firmicutes 632 1 1 Proteobacteria 1638 1 2 Actinobacteria 569 1 3 Bacteroidetes 115 1 4 Firmicutes 433 2 5 Proteobacteria 1130 2 6 Actinobacteria 754 2 7 Bacteroidetes 555 2
A DataFrame
has a second index, representing the columns:
data.columns
Index([u'patient', u'phylum', u'value'], dtype='object')
If we wish to access columns, we can do so either by dict-like indexing or by attribute:
data['value']
0 632 1 1638 2 569 3 115 4 433 5 1130 6 754 7 555 Name: value, dtype: int64
data.value
0 632 1 1638 2 569 3 115 4 433 5 1130 6 754 7 555 Name: value, dtype: int64
type(data.value)
pandas.core.series.Series
type(data[['value']])
pandas.core.frame.DataFrame
Notice this is different than with Series
, where dict-like indexing retrieved a particular element (row). If we want access to a row in a DataFrame
, we index its ix
attribute.
data.ix[3]
patient 1 phylum Bacteroidetes value 115 Name: 3, dtype: object
Alternatively, we can create a DataFrame
with a dict of dicts:
data = pd.DataFrame({0: {'patient': 1, 'phylum': 'Firmicutes', 'value': 632},
1: {'patient': 1, 'phylum': 'Proteobacteria', 'value': 1638},
2: {'patient': 1, 'phylum': 'Actinobacteria', 'value': 569},
3: {'patient': 1, 'phylum': 'Bacteroidetes', 'value': 115},
4: {'patient': 2, 'phylum': 'Firmicutes', 'value': 433},
5: {'patient': 2, 'phylum': 'Proteobacteria', 'value': 1130},
6: {'patient': 2, 'phylum': 'Actinobacteria', 'value': 754},
7: {'patient': 2, 'phylum': 'Bacteroidetes', 'value': 555}})
data
0 1 2 3 \ patient 1 1 1 1 phylum Firmicutes Proteobacteria Actinobacteria Bacteroidetes value 632 1638 569 115 4 5 6 7 patient 2 2 2 2 phylum Firmicutes Proteobacteria Actinobacteria Bacteroidetes value 433 1130 754 555
We probably want this transposed:
data = data.T
data
patient phylum value 0 1 Firmicutes 632 1 1 Proteobacteria 1638 2 1 Actinobacteria 569 3 1 Bacteroidetes 115 4 2 Firmicutes 433 5 2 Proteobacteria 1130 6 2 Actinobacteria 754 7 2 Bacteroidetes 555
Its important to note that the Series returned when a DataFrame is indexted is merely a view on the DataFrame, and not a copy of the data itself. So you must be cautious when manipulating this data:
vals = data.value
vals
0 632 1 1638 2 569 3 115 4 433 5 1130 6 754 7 555 Name: value, dtype: object
vals[5] = 0
vals
0 632 1 1638 2 569 3 115 4 433 5 0 6 754 7 555 Name: value, dtype: object
data
patient phylum value 0 1 Firmicutes 632 1 1 Proteobacteria 1638 2 1 Actinobacteria 569 3 1 Bacteroidetes 115 4 2 Firmicutes 433 5 2 Proteobacteria 0 6 2 Actinobacteria 754 7 2 Bacteroidetes 555
vals = data.value.copy()
vals[5] = 1000
data
patient phylum value 0 1 Firmicutes 632 1 1 Proteobacteria 1638 2 1 Actinobacteria 569 3 1 Bacteroidetes 115 4 2 Firmicutes 433 5 2 Proteobacteria 0 6 2 Actinobacteria 754 7 2 Bacteroidetes 555
We can create or modify columns by assignment:
data.value[3] = 14
data
patient phylum value 0 1 Firmicutes 632 1 1 Proteobacteria 1638 2 1 Actinobacteria 569 3 1 Bacteroidetes 14 4 2 Firmicutes 433 5 2 Proteobacteria 0 6 2 Actinobacteria 754 7 2 Bacteroidetes 555
data['year'] = 2013
data
patient phylum value year 0 1 Firmicutes 632 2013 1 1 Proteobacteria 1638 2013 2 1 Actinobacteria 569 2013 3 1 Bacteroidetes 14 2013 4 2 Firmicutes 433 2013 5 2 Proteobacteria 0 2013 6 2 Actinobacteria 754 2013 7 2 Bacteroidetes 555 2013
But note, we cannot use the attribute indexing method to add a new column:
data.treatment = 1
data
patient phylum value year 0 1 Firmicutes 632 2013 1 1 Proteobacteria 1638 2013 2 1 Actinobacteria 569 2013 3 1 Bacteroidetes 14 2013 4 2 Firmicutes 433 2013 5 2 Proteobacteria 0 2013 6 2 Actinobacteria 754 2013 7 2 Bacteroidetes 555 2013
data.treatment
1
Specifying a Series
as a new columns cause its values to be added according to the DataFrame
's index:
treatment = pd.Series([0]*4 + [1]*2)
treatment
0 0 1 0 2 0 3 0 4 1 5 1 dtype: int64
data['treatment'] = treatment
data
patient phylum value year treatment 0 1 Firmicutes 632 2013 0 1 1 Proteobacteria 1638 2013 0 2 1 Actinobacteria 569 2013 0 3 1 Bacteroidetes 14 2013 0 4 2 Firmicutes 433 2013 1 5 2 Proteobacteria 0 2013 1 6 2 Actinobacteria 754 2013 NaN 7 2 Bacteroidetes 555 2013 NaN
Other Python data structures (ones without an index) need to be the same length as the DataFrame
:
month = ['Jan', 'Feb', 'Mar', 'Apr']
data['month'] = month
--------------------------------------------------------------------------- ValueError Traceback (most recent call last) <ipython-input-39-360d03fdde9a> in <module>() 1 month = ['Jan', 'Feb', 'Mar', 'Apr'] ----> 2 data['month'] = month /usr/local/lib/python2.7/site-packages/pandas/core/frame.pyc in __setitem__(self, key, value) 1899 else: 1900 # set column -> 1901 self._set_item(key, value) 1902 1903 def _setitem_slice(self, key, value): /usr/local/lib/python2.7/site-packages/pandas/core/frame.pyc in _set_item(self, key, value) 1981 is_existing = key in self.columns 1982 self._ensure_valid_index(value) -> 1983 value = self._sanitize_column(key, value) 1984 NDFrame._set_item(self, key, value) 1985 /usr/local/lib/python2.7/site-packages/pandas/core/frame.pyc in _sanitize_column(self, key, value) 2031 elif isinstance(value, Index) or _is_sequence(value): 2032 if len(value) != len(self.index): -> 2033 raise ValueError('Length of values does not match length of ' 2034 'index') 2035 ValueError: Length of values does not match length of index
data['month'] = ['Jan']*len(data)
data
patient phylum value year treatment month 0 1 Firmicutes 632 2013 0 Jan 1 1 Proteobacteria 1638 2013 0 Jan 2 1 Actinobacteria 569 2013 0 Jan 3 1 Bacteroidetes 14 2013 0 Jan 4 2 Firmicutes 433 2013 1 Jan 5 2 Proteobacteria 0 2013 1 Jan 6 2 Actinobacteria 754 2013 NaN Jan 7 2 Bacteroidetes 555 2013 NaN Jan
We can use del
to remove columns, in the same way dict
entries can be removed:
del data['month']
data
patient phylum value year treatment 0 1 Firmicutes 632 2013 0 1 1 Proteobacteria 1638 2013 0 2 1 Actinobacteria 569 2013 0 3 1 Bacteroidetes 14 2013 0 4 2 Firmicutes 433 2013 1 5 2 Proteobacteria 0 2013 1 6 2 Actinobacteria 754 2013 NaN 7 2 Bacteroidetes 555 2013 NaN
We can extract the underlying data as a simple ndarray
by accessing the values
attribute:
data.values
array([[1, 'Firmicutes', 632, 2013, 0.0], [1, 'Proteobacteria', 1638, 2013, 0.0], [1, 'Actinobacteria', 569, 2013, 0.0], [1, 'Bacteroidetes', 14, 2013, 0.0], [2, 'Firmicutes', 433, 2013, 1.0], [2, 'Proteobacteria', 0, 2013, 1.0], [2, 'Actinobacteria', 754, 2013, nan], [2, 'Bacteroidetes', 555, 2013, nan]], dtype=object)
Notice that because of the mix of string and integer (and NaN
) values, the dtype of the array is object
. The dtype will automatically be chosen to be as general as needed to accomodate all the columns.
df = pd.DataFrame({'foo': [1,2,3], 'bar':[0.4, -1.0, 4.5]})
df.values
array([[ 0.4, 1. ], [-1. , 2. ], [ 4.5, 3. ]])
Pandas uses a custom data structure to represent the indices of Series and DataFrames.
data.index
Int64Index([0, 1, 2, 3, 4, 5, 6, 7], dtype='int64')
Index objects are immutable:
data.index[0] = 15
--------------------------------------------------------------------------- TypeError Traceback (most recent call last) <ipython-input-45-42a852cc9eac> in <module>() ----> 1 data.index[0] = 15 /usr/local/lib/python2.7/site-packages/pandas/core/base.pyc in _disabled(self, *args, **kwargs) 177 """This method will not function because object is immutable.""" 178 raise TypeError("'%s' does not support mutable operations." % --> 179 self.__class__) 180 181 __setitem__ = __setslice__ = __delitem__ = __delslice__ = _disabled TypeError: '<class 'pandas.core.index.Int64Index'>' does not support mutable operations.
This is so that Index objects can be shared between data structures without fear that they will be changed.
bacteria2.index = bacteria.index
bacteria2
phylum Firmicutes NaN Proteobacteria 632 Actinobacteria 1638 Bacteroidetes 569 dtype: float64
A key, but often under-appreciated, step in data analysis is importing the data that we wish to analyze. Though it is easy to load basic data structures into Python using built-in tools or those provided by packages like NumPy, it is non-trivial to import structured data well, and to easily convert this input into a robust data structure:
genes = np.loadtxt("genes.csv", delimiter=",", dtype=[('gene', '|S10'), ('value', '<f4')])
Pandas provides a convenient set of functions for importing tabular data in a number of formats directly into a DataFrame
object. These functions include a slew of options to perform type inference, indexing, parsing, iterating and cleaning automatically as data are imported.
Let's start with some more bacteria data, stored in csv format.
!cat data/microbiome.csv
Taxon,Patient,Tissue,Stool Firmicutes,1,632,305 Firmicutes,2,136,4182 Firmicutes,3,1174,703 Firmicutes,4,408,3946 Firmicutes,5,831,8605 Firmicutes,6,693,50 Firmicutes,7,718,717 Firmicutes,8,173,33 Firmicutes,9,228,80 Firmicutes,10,162,3196 Firmicutes,11,372,32 Firmicutes,12,4255,4361 Firmicutes,13,107,1667 Firmicutes,14,96,223 Firmicutes,15,281,2377 Proteobacteria,1,1638,3886 Proteobacteria,2,2469,1821 Proteobacteria,3,839,661 Proteobacteria,4,4414,18 Proteobacteria,5,12044,83 Proteobacteria,6,2310,12 Proteobacteria,7,3053,547 Proteobacteria,8,395,2174 Proteobacteria,9,2651,767 Proteobacteria,10,1195,76 Proteobacteria,11,6857,795 Proteobacteria,12,483,666 Proteobacteria,13,2950,3994 Proteobacteria,14,1541,816 Proteobacteria,15,1307,53 Actinobacteria,1,569,648 Actinobacteria,2,1590,4 Actinobacteria,3,25,2 Actinobacteria,4,259,300 Actinobacteria,5,568,7 Actinobacteria,6,1102,9 Actinobacteria,7,678,377 Actinobacteria,8,260,58 Actinobacteria,9,424,233 Actinobacteria,10,548,21 Actinobacteria,11,201,83 Actinobacteria,12,42,75 Actinobacteria,13,109,59 Actinobacteria,14,51,183 Actinobacteria,15,310,204 Bacteroidetes,1,115,380 Bacteroidetes,2,67,0 Bacteroidetes,3,0,0 Bacteroidetes,4,85,5 Bacteroidetes,5,143,7 Bacteroidetes,6,678,2 Bacteroidetes,7,4829,209 Bacteroidetes,8,74,651 Bacteroidetes,9,169,254 Bacteroidetes,10,106,10 Bacteroidetes,11,73,381 Bacteroidetes,12,30,359 Bacteroidetes,13,51,51 Bacteroidetes,14,2473,2314 Bacteroidetes,15,102,33 Other,1,114,277 Other,2,195,18 Other,3,42,2 Other,4,316,43 Other,5,202,40 Other,6,116,0 Other,7,527,12 Other,8,357,11 Other,9,106,11 Other,10,67,14 Other,11,203,6 Other,12,392,6 Other,13,28,25 Other,14,12,22 Other,15,305,32
This table can be read into a DataFrame using read_csv
:
mb = pd.read_csv("data/microbiome.csv")
mb
Taxon Patient Tissue Stool 0 Firmicutes 1 632 305 1 Firmicutes 2 136 4182 2 Firmicutes 3 1174 703 3 Firmicutes 4 408 3946 4 Firmicutes 5 831 8605 5 Firmicutes 6 693 50 6 Firmicutes 7 718 717 7 Firmicutes 8 173 33 8 Firmicutes 9 228 80 9 Firmicutes 10 162 3196 .. ... ... ... ... 65 Other 6 116 0 66 Other 7 527 12 67 Other 8 357 11 68 Other 9 106 11 69 Other 10 67 14 70 Other 11 203 6 71 Other 12 392 6 72 Other 13 28 25 73 Other 14 12 22 74 Other 15 305 32 [75 rows x 4 columns]
Notice that read_csv
automatically considered the first row in the file to be a header row.
We can override default behavior by customizing some the arguments, like header
, names
or index_col
.
pd.read_csv("data/microbiome.csv", header=None).head()
0 1 2 3 0 Taxon Patient Tissue Stool 1 Firmicutes 1 632 305 2 Firmicutes 2 136 4182 3 Firmicutes 3 1174 703 4 Firmicutes 4 408 3946
read_csv
is just a convenience function for read_table
, since csv is such a common format:
mb = pd.read_table("data/microbiome.csv", sep=',')
The sep
argument can be customized as needed to accomodate arbitrary separators. For example, we can use a regular expression to define a variable amount of whitespace, which is unfortunately very common in some data formats:
sep='\s+'
For a more useful index, we can specify the first two columns, which together provide a unique index to the data.
mb = pd.read_csv("data/microbiome.csv", index_col=['Taxon','Patient'])
mb.head()
Tissue Stool Taxon Patient Firmicutes 1 632 305 2 136 4182 3 1174 703 4 408 3946 5 831 8605
This is called a hierarchical index, which we will revisit later in the tutorial.
If we have sections of data that we do not wish to import (for example, known bad data), we can populate the skiprows
argument:
pd.read_csv("data/microbiome.csv", skiprows=[3,4,6]).head()
Taxon Patient Tissue Stool 0 Firmicutes 1 632 305 1 Firmicutes 2 136 4182 2 Firmicutes 5 831 8605 3 Firmicutes 7 718 717 4 Firmicutes 8 173 33
Conversely, if we only want to import a small number of rows from, say, a very large data file we can use nrows
:
pd.read_csv("data/microbiome.csv", nrows=4)
Taxon Patient Tissue Stool 0 Firmicutes 1 632 305 1 Firmicutes 2 136 4182 2 Firmicutes 3 1174 703 3 Firmicutes 4 408 3946
Alternately, if we want to process our data in reasonable chunks, the chunksize
argument will return an iterable object that can be employed in a data processing loop. For example, our microbiome data are organized by bacterial phylum, with 15 patients represented in each:
data_chunks = pd.read_csv("data/microbiome.csv", chunksize=15)
mean_tissue = {chunk.Taxon[0]:chunk.Tissue.mean() for chunk in data_chunks}
mean_tissue
{'Actinobacteria': 449.06666666666666, 'Bacteroidetes': 599.66666666666663, 'Firmicutes': 684.39999999999998, 'Other': 198.80000000000001, 'Proteobacteria': 2943.0666666666666}
Most real-world data is incomplete, with values missing due to incomplete observation, data entry or transcription error, or other reasons. Pandas will automatically recognize and parse common missing data indicators, including NA
and NULL
.
!cat data/microbiome_missing.csv
Taxon,Patient,Tissue,Stool Firmicutes,1,632,305 Firmicutes,2,136,4182 Firmicutes,3,,703 Firmicutes,4,408,3946 Firmicutes,5,831,8605 Firmicutes,6,693,50 Firmicutes,7,718,717 Firmicutes,8,173,33 Firmicutes,9,228,NA Firmicutes,10,162,3196 Firmicutes,11,372,-99999 Firmicutes,12,4255,4361 Firmicutes,13,107,1667 Firmicutes,14,?,223 Firmicutes,15,281,2377 Proteobacteria,1,1638,3886 Proteobacteria,2,2469,1821 Proteobacteria,3,839,661 Proteobacteria,4,4414,18 Proteobacteria,5,12044,83 Proteobacteria,6,2310,12 Proteobacteria,7,3053,547 Proteobacteria,8,395,2174 Proteobacteria,9,2651,767 Proteobacteria,10,1195,76 Proteobacteria,11,6857,795 Proteobacteria,12,483,666 Proteobacteria,13,2950,3994 Proteobacteria,14,1541,816 Proteobacteria,15,1307,53 Actinobacteria,1,569,648 Actinobacteria,2,1590,4 Actinobacteria,3,25,2 Actinobacteria,4,259,300 Actinobacteria,5,568,7 Actinobacteria,6,1102,9 Actinobacteria,7,678,377 Actinobacteria,8,260,58 Actinobacteria,9,424,233 Actinobacteria,10,548,21 Actinobacteria,11,201,83 Actinobacteria,12,42,75 Actinobacteria,13,109,59 Actinobacteria,14,51,183 Actinobacteria,15,310,204 Bacteroidetes,1,115,380 Bacteroidetes,2,67,0 Bacteroidetes,3,0,0 Bacteroidetes,4,85,5 Bacteroidetes,5,143,7 Bacteroidetes,6,678,2 Bacteroidetes,7,4829,209 Bacteroidetes,8,74,651 Bacteroidetes,9,169,254 Bacteroidetes,10,106,10 Bacteroidetes,11,73,381 Bacteroidetes,12,30,359 Bacteroidetes,13,51,51 Bacteroidetes,14,2473,2314 Bacteroidetes,15,102,33 Other,1,114,277 Other,2,195,18 Other,3,42,2 Other,4,316,43 Other,5,202,40 Other,6,116,0 Other,7,527,12 Other,8,357,11 Other,9,106,11 Other,10,67,14 Other,11,203,6 Other,12,392,6 Other,13,28,25 Other,14,12,22 Other,15,305,32
pd.read_csv("data/microbiome_missing.csv").head(20)
Taxon Patient Tissue Stool 0 Firmicutes 1 632 305 1 Firmicutes 2 136 4182 2 Firmicutes 3 NaN 703 3 Firmicutes 4 408 3946 4 Firmicutes 5 831 8605 5 Firmicutes 6 693 50 6 Firmicutes 7 718 717 7 Firmicutes 8 173 33 8 Firmicutes 9 228 NaN 9 Firmicutes 10 162 3196 10 Firmicutes 11 372 -99999 11 Firmicutes 12 4255 4361 12 Firmicutes 13 107 1667 13 Firmicutes 14 ? 223 14 Firmicutes 15 281 2377 15 Proteobacteria 1 1638 3886 16 Proteobacteria 2 2469 1821 17 Proteobacteria 3 839 661 18 Proteobacteria 4 4414 18 19 Proteobacteria 5 12044 83
Above, Pandas recognized NA
and an empty field as missing data.
pd.isnull(pd.read_csv("data/microbiome_missing.csv")).head(20)
Taxon Patient Tissue Stool 0 False False False False 1 False False False False 2 False False True False 3 False False False False 4 False False False False 5 False False False False 6 False False False False 7 False False False False 8 False False False True 9 False False False False 10 False False False False 11 False False False False 12 False False False False 13 False False False False 14 False False False False 15 False False False False 16 False False False False 17 False False False False 18 False False False False 19 False False False False
Unfortunately, there will sometimes be inconsistency with the conventions for missing data. In this example, there is a question mark "?" and a large negative number where there should have been a positive integer. We can specify additional symbols with the na_values
argument:
pd.read_csv("data/microbiome_missing.csv", na_values=['?', -99999]).head(20)
Taxon Patient Tissue Stool 0 Firmicutes 1 632 305 1 Firmicutes 2 136 4182 2 Firmicutes 3 NaN 703 3 Firmicutes 4 408 3946 4 Firmicutes 5 831 8605 5 Firmicutes 6 693 50 6 Firmicutes 7 718 717 7 Firmicutes 8 173 33 8 Firmicutes 9 228 NaN 9 Firmicutes 10 162 3196 10 Firmicutes 11 372 NaN 11 Firmicutes 12 4255 4361 12 Firmicutes 13 107 1667 13 Firmicutes 14 NaN 223 14 Firmicutes 15 281 2377 15 Proteobacteria 1 1638 3886 16 Proteobacteria 2 2469 1821 17 Proteobacteria 3 839 661 18 Proteobacteria 4 4414 18 19 Proteobacteria 5 12044 83
These can be specified on a column-wise basis using an appropriate dict as the argument for na_values
.
Since so much financial and scientific data ends up in Excel spreadsheets (regrettably), Pandas' ability to directly import Excel spreadsheets is valuable. This support is contingent on having one or two dependencies (depending on what version of Excel file is being imported) installed: xlrd
and openpyxl
(these may be installed with either pip
or easy_install
).
Importing Excel data to Pandas is a two-step process. First, we create an ExcelFile
object using the path of the file:
mb_file = pd.ExcelFile('data/microbiome/MID1.xls')
mb_file
<pandas.io.excel.ExcelFile at 0x10f8426d0>
Then, since modern spreadsheets consist of one or more "sheets", we parse the sheet with the data of interest:
mb1 = mb_file.parse("Sheet 1", header=None)
mb1.columns = ["Taxon", "Count"]
mb1.head()
Taxon Count 0 Archaea "Crenarchaeota" Thermoprotei Desulfuro... 7 1 Archaea "Crenarchaeota" Thermoprotei Desulfuro... 2 2 Archaea "Crenarchaeota" Thermoprotei Sulfoloba... 3 3 Archaea "Crenarchaeota" Thermoprotei Thermopro... 3 4 Archaea "Euryarchaeota" "Methanomicrobia" Meth... 7
There is now a read_excel
conveneince function in Pandas that combines these steps into a single call:
mb2 = pd.read_excel('data/microbiome/MID2.xls', sheetname='Sheet 1', header=None)
mb2.head()
0 1 0 Archaea "Crenarchaeota" Thermoprotei Acidiloba... 2 1 Archaea "Crenarchaeota" Thermoprotei Acidiloba... 14 2 Archaea "Crenarchaeota" Thermoprotei Desulfuro... 23 3 Archaea "Crenarchaeota" Thermoprotei Desulfuro... 1 4 Archaea "Crenarchaeota" Thermoprotei Desulfuro... 2
There are several other data formats that can be imported into Python and converted into DataFrames, with the help of buitl-in or third-party libraries. These include JSON, XML, HDF5, relational and non-relational databases, and various web APIs. These are beyond the scope of this tutorial, but are covered in Python for Data Analysis.
This section introduces the new user to the key functionality of Pandas that is required to use the software effectively.
For some variety, we will leave our digestive tract bacteria behind and employ some baseball data.
baseball = pd.read_csv("data/baseball.csv", index_col='id')
baseball.head()
player year stint team lg g ab r h X2b X3b hr rbi sb \ id 88641 womacto01 2006 2 CHN NL 19 50 6 14 1 0 1 2 1 88643 schilcu01 2006 1 BOS AL 31 2 0 1 0 0 0 0 0 88645 myersmi01 2006 1 NYA AL 62 0 0 0 0 0 0 0 0 88649 helliri01 2006 1 MIL NL 20 3 0 0 0 0 0 0 0 88650 johnsra05 2006 1 NYA AL 33 6 0 1 0 0 0 0 0 cs bb so ibb hbp sh sf gidp id 88641 1 4 4 0 0 3 0 0 88643 0 0 1 0 0 0 0 0 88645 0 0 0 0 0 0 0 0 88649 0 0 2 0 0 0 0 0 88650 0 0 4 0 0 0 0 0
Notice that we specified the id
column as the index, since it appears to be a unique identifier. We could try to create a unique index ourselves by combining player
and year
:
player_id = baseball.player + baseball.year.astype(str)
baseball_newind = baseball.copy()
baseball_newind.index = player_id
baseball_newind.head()
player year stint team lg g ab r h X2b X3b hr \ womacto012006 womacto01 2006 2 CHN NL 19 50 6 14 1 0 1 schilcu012006 schilcu01 2006 1 BOS AL 31 2 0 1 0 0 0 myersmi012006 myersmi01 2006 1 NYA AL 62 0 0 0 0 0 0 helliri012006 helliri01 2006 1 MIL NL 20 3 0 0 0 0 0 johnsra052006 johnsra05 2006 1 NYA AL 33 6 0 1 0 0 0 rbi sb cs bb so ibb hbp sh sf gidp womacto012006 2 1 1 4 4 0 0 3 0 0 schilcu012006 0 0 0 0 1 0 0 0 0 0 myersmi012006 0 0 0 0 0 0 0 0 0 0 helliri012006 0 0 0 0 2 0 0 0 0 0 johnsra052006 0 0 0 0 4 0 0 0 0 0
This looks okay, but let's check:
baseball_newind.index.is_unique
False
So, indices need not be unique. Our choice is not unique because some players change teams within years.
pd.Series(baseball_newind.index).value_counts()
wickmbo012007 2 gomezch022007 2 sweenma012007 2 claytro012007 2 hernaro012007 2 loftoke012007 2 trachst012007 2 wellsda012007 2 ... myersmi012007 1 alomasa022007 1 edmonji012007 1 sheffga012007 1 whiteri012007 1 cormirh012007 1 floydcl012007 1 embreal012007 1 Length: 88, dtype: int64
The most important consequence of a non-unique index is that indexing by label will return multiple values for some labels:
baseball_newind.ix['wickmbo012007']
player year stint team lg g ab r h X2b X3b hr \ wickmbo012007 wickmbo01 2007 2 ARI NL 8 0 0 0 0 0 0 wickmbo012007 wickmbo01 2007 1 ATL NL 47 0 0 0 0 0 0 rbi sb cs bb so ibb hbp sh sf gidp wickmbo012007 0 0 0 0 0 0 0 0 0 0 wickmbo012007 0 0 0 0 0 0 0 0 0 0
We will learn more about indexing below.
We can create a truly unique index by combining player
, team
and year
:
player_unique = baseball.player + baseball.team + baseball.year.astype(str)
baseball_newind = baseball.copy()
baseball_newind.index = player_unique
baseball_newind.head()
player year stint team lg g ab r h X2b X3b \ womacto01CHN2006 womacto01 2006 2 CHN NL 19 50 6 14 1 0 schilcu01BOS2006 schilcu01 2006 1 BOS AL 31 2 0 1 0 0 myersmi01NYA2006 myersmi01 2006 1 NYA AL 62 0 0 0 0 0 helliri01MIL2006 helliri01 2006 1 MIL NL 20 3 0 0 0 0 johnsra05NYA2006 johnsra05 2006 1 NYA AL 33 6 0 1 0 0 hr rbi sb cs bb so ibb hbp sh sf gidp womacto01CHN2006 1 2 1 1 4 4 0 0 3 0 0 schilcu01BOS2006 0 0 0 0 0 1 0 0 0 0 0 myersmi01NYA2006 0 0 0 0 0 0 0 0 0 0 0 helliri01MIL2006 0 0 0 0 0 2 0 0 0 0 0 johnsra05NYA2006 0 0 0 0 0 4 0 0 0 0 0
baseball_newind.index.is_unique
True
We can create meaningful indices more easily using a hierarchical index; for now, we will stick with the numeric id
field as our index.
Reindexing allows users to manipulate the data labels in a DataFrame. It forces a DataFrame to conform to the new index, and optionally, fill in missing data if requested.
A simple use of reindex
is to alter the order of the rows:
baseball.reindex(baseball.index[::-1]).head()
player year stint team lg g ab r h X2b X3b hr rbi \ id 89534 alomasa02 2007 1 NYN NL 8 22 1 3 1 0 0 0 89533 aloumo01 2007 1 NYN NL 87 328 51 112 19 1 13 49 89530 ausmubr01 2007 1 HOU NL 117 349 38 82 16 3 3 25 89526 benitar01 2007 1 SFN NL 19 0 0 0 0 0 0 0 89525 benitar01 2007 2 FLO NL 34 0 0 0 0 0 0 0 sb cs bb so ibb hbp sh sf gidp id 89534 0 0 0 3 0 0 0 0 0 89533 3 0 27 30 5 2 0 3 13 89530 6 1 37 74 3 6 4 1 11 89526 0 0 0 0 0 0 0 0 0 89525 0 0 0 0 0 0 0 0 0
Notice that the id
index is not sequential. Say we wanted to populate the table with every id
value. We could specify and index that is a sequence from the first to the last id
numbers in the database, and Pandas would fill in the missing data with NaN
values:
id_range = range(baseball.index.values.min(), baseball.index.values.max())
baseball.reindex(id_range).head()
player year stint team lg g ab r h X2b X3b hr rbi \ 88641 womacto01 2006 2 CHN NL 19 50 6 14 1 0 1 2 88642 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 88643 schilcu01 2006 1 BOS AL 31 2 0 1 0 0 0 0 88644 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 88645 myersmi01 2006 1 NYA AL 62 0 0 0 0 0 0 0 sb cs bb so ibb hbp sh sf gidp 88641 1 1 4 4 0 0 3 0 0 88642 NaN NaN NaN NaN NaN NaN NaN NaN NaN 88643 0 0 0 1 0 0 0 0 0 88644 NaN NaN NaN NaN NaN NaN NaN NaN NaN 88645 0 0 0 0 0 0 0 0 0
Missing values can be filled as desired, either with selected values, or by rule:
baseball.reindex(id_range, method='ffill', columns=['player','year']).head()
player year 88641 womacto01 2006 88642 womacto01 2006 88643 schilcu01 2006 88644 schilcu01 2006 88645 myersmi01 2006
baseball.reindex(id_range, fill_value='mr.nobody', columns=['player']).head()
player 88641 womacto01 88642 mr.nobody 88643 schilcu01 88644 mr.nobody 88645 myersmi01
Keep in mind that reindex
does not work if we pass a non-unique index series.
We can remove rows or columns via the drop
method:
baseball.shape
(100, 22)
baseball.drop([89525, 89526])
player year stint team lg g ab r h X2b X3b hr rbi \ id 88641 womacto01 2006 2 CHN NL 19 50 6 14 1 0 1 2 88643 schilcu01 2006 1 BOS AL 31 2 0 1 0 0 0 0 88645 myersmi01 2006 1 NYA AL 62 0 0 0 0 0 0 0 88649 helliri01 2006 1 MIL NL 20 3 0 0 0 0 0 0 88650 johnsra05 2006 1 NYA AL 33 6 0 1 0 0 0 0 88652 finlest01 2006 1 SFN NL 139 426 66 105 21 12 6 40 88653 gonzalu01 2006 1 ARI NL 153 586 93 159 52 2 15 73 88662 seleaa01 2006 1 LAN NL 28 26 2 5 1 0 0 0 89177 francju01 2007 2 ATL NL 15 40 1 10 3 0 0 8 89178 francju01 2007 1 NYN NL 40 50 7 10 0 0 1 8 ... ... ... ... ... .. ... ... .. ... ... ... .. ... 89497 clemero02 2007 1 NYA AL 2 2 0 1 0 0 0 0 89498 claytro01 2007 2 BOS AL 8 6 1 0 0 0 0 0 89499 claytro01 2007 1 TOR AL 69 189 23 48 14 0 1 12 89501 cirilje01 2007 2 ARI NL 28 40 6 8 4 0 0 6 89502 cirilje01 2007 1 MIN AL 50 153 18 40 9 2 2 21 89521 bondsba01 2007 1 SFN NL 126 340 75 94 14 0 28 66 89523 biggicr01 2007 1 HOU NL 141 517 68 130 31 3 10 50 89530 ausmubr01 2007 1 HOU NL 117 349 38 82 16 3 3 25 89533 aloumo01 2007 1 NYN NL 87 328 51 112 19 1 13 49 89534 alomasa02 2007 1 NYN NL 8 22 1 3 1 0 0 0 sb cs bb so ibb hbp sh sf gidp id 88641 1 1 4 4 0 0 3 0 0 88643 0 0 0 1 0 0 0 0 0 88645 0 0 0 0 0 0 0 0 0 88649 0 0 0 2 0 0 0 0 0 88650 0 0 0 4 0 0 0 0 0 88652 7 0 46 55 2 2 3 4 6 88653 0 1 69 58 10 7 0 6 14 88662 0 0 1 7 0 0 6 0 1 89177 0 0 4 10 1 0 0 1 1 89178 2 1 10 13 0 0 0 1 1 ... .. .. ... ... ... ... .. .. ... 89497 0 0 0 0 0 0 0 0 0 89498 0 0 0 3 0 0 0 0 2 89499 2 1 14 50 0 1 3 3 8 89501 0 0 4 6 0 0 0 0 1 89502 2 0 15 13 0 1 3 2 9 89521 5 0 132 54 43 3 0 2 13 89523 4 3 23 112 0 3 7 5 5 89530 6 1 37 74 3 6 4 1 11 89533 3 0 27 30 5 2 0 3 13 89534 0 0 0 3 0 0 0 0 0 [98 rows x 22 columns]
baseball.drop(['ibb','hbp'], axis=1)
player year stint team lg g ab r h X2b X3b hr rbi \ id 88641 womacto01 2006 2 CHN NL 19 50 6 14 1 0 1 2 88643 schilcu01 2006 1 BOS AL 31 2 0 1 0 0 0 0 88645 myersmi01 2006 1 NYA AL 62 0 0 0 0 0 0 0 88649 helliri01 2006 1 MIL NL 20 3 0 0 0 0 0 0 88650 johnsra05 2006 1 NYA AL 33 6 0 1 0 0 0 0 88652 finlest01 2006 1 SFN NL 139 426 66 105 21 12 6 40 88653 gonzalu01 2006 1 ARI NL 153 586 93 159 52 2 15 73 88662 seleaa01 2006 1 LAN NL 28 26 2 5 1 0 0 0 89177 francju01 2007 2 ATL NL 15 40 1 10 3 0 0 8 89178 francju01 2007 1 NYN NL 40 50 7 10 0 0 1 8 ... ... ... ... ... .. ... ... .. ... ... ... .. ... 89499 claytro01 2007 1 TOR AL 69 189 23 48 14 0 1 12 89501 cirilje01 2007 2 ARI NL 28 40 6 8 4 0 0 6 89502 cirilje01 2007 1 MIN AL 50 153 18 40 9 2 2 21 89521 bondsba01 2007 1 SFN NL 126 340 75 94 14 0 28 66 89523 biggicr01 2007 1 HOU NL 141 517 68 130 31 3 10 50 89525 benitar01 2007 2 FLO NL 34 0 0 0 0 0 0 0 89526 benitar01 2007 1 SFN NL 19 0 0 0 0 0 0 0 89530 ausmubr01 2007 1 HOU NL 117 349 38 82 16 3 3 25 89533 aloumo01 2007 1 NYN NL 87 328 51 112 19 1 13 49 89534 alomasa02 2007 1 NYN NL 8 22 1 3 1 0 0 0 sb cs bb so sh sf gidp id 88641 1 1 4 4 3 0 0 88643 0 0 0 1 0 0 0 88645 0 0 0 0 0 0 0 88649 0 0 0 2 0 0 0 88650 0 0 0 4 0 0 0 88652 7 0 46 55 3 4 6 88653 0 1 69 58 0 6 14 88662 0 0 1 7 6 0 1 89177 0 0 4 10 0 1 1 89178 2 1 10 13 0 1 1 ... .. .. ... ... .. .. ... 89499 2 1 14 50 3 3 8 89501 0 0 4 6 0 0 1 89502 2 0 15 13 3 2 9 89521 5 0 132 54 0 2 13 89523 4 3 23 112 7 5 5 89525 0 0 0 0 0 0 0 89526 0 0 0 0 0 0 0 89530 6 1 37 74 4 1 11 89533 3 0 27 30 0 3 13 89534 0 0 0 3 0 0 0 [100 rows x 20 columns]
Indexing works analogously to indexing in NumPy arrays, except we can use the labels in the Index
object to extract values in addition to arrays of integers.
# Sample Series object
hits = baseball_newind.h
hits
womacto01CHN2006 14 schilcu01BOS2006 1 myersmi01NYA2006 0 helliri01MIL2006 0 johnsra05NYA2006 1 finlest01SFN2006 105 gonzalu01ARI2006 159 seleaa01LAN2006 5 ... cirilje01MIN2007 40 bondsba01SFN2007 94 biggicr01HOU2007 130 benitar01FLO2007 0 benitar01SFN2007 0 ausmubr01HOU2007 82 aloumo01NYN2007 112 alomasa02NYN2007 3 Name: h, Length: 100, dtype: int64
# Numpy-style indexing
hits[:3]
womacto01CHN2006 14 schilcu01BOS2006 1 myersmi01NYA2006 0 Name: h, dtype: int64
# Indexing by label
hits[['womacto01CHN2006','schilcu01BOS2006']]
womacto01CHN2006 14 schilcu01BOS2006 1 Name: h, dtype: int64
We can also slice with data labels, since they have an intrinsic order within the Index:
hits['womacto01CHN2006':'gonzalu01ARI2006']
womacto01CHN2006 14 schilcu01BOS2006 1 myersmi01NYA2006 0 helliri01MIL2006 0 johnsra05NYA2006 1 finlest01SFN2006 105 gonzalu01ARI2006 159 Name: h, dtype: int64
hits['womacto01CHN2006':'gonzalu01ARI2006'] = 5
hits
womacto01CHN2006 5 schilcu01BOS2006 5 myersmi01NYA2006 5 helliri01MIL2006 5 johnsra05NYA2006 5 finlest01SFN2006 5 gonzalu01ARI2006 5 seleaa01LAN2006 5 ... cirilje01MIN2007 40 bondsba01SFN2007 94 biggicr01HOU2007 130 benitar01FLO2007 0 benitar01SFN2007 0 ausmubr01HOU2007 82 aloumo01NYN2007 112 alomasa02NYN2007 3 Name: h, Length: 100, dtype: int64
In a DataFrame
we can slice along either or both axes:
baseball_newind[['h','ab']]
h ab womacto01CHN2006 5 50 schilcu01BOS2006 5 2 myersmi01NYA2006 5 0 helliri01MIL2006 5 3 johnsra05NYA2006 5 6 finlest01SFN2006 5 426 gonzalu01ARI2006 5 586 seleaa01LAN2006 5 26 francju01ATL2007 10 40 francju01NYN2007 10 50 ... ... ... claytro01TOR2007 48 189 cirilje01ARI2007 8 40 cirilje01MIN2007 40 153 bondsba01SFN2007 94 340 biggicr01HOU2007 130 517 benitar01FLO2007 0 0 benitar01SFN2007 0 0 ausmubr01HOU2007 82 349 aloumo01NYN2007 112 328 alomasa02NYN2007 3 22 [100 rows x 2 columns]
baseball_newind[baseball_newind.ab>500]
player year stint team lg g ab r h X2b \ gonzalu01ARI2006 gonzalu01 2006 1 ARI NL 153 586 93 5 52 vizquom01SFN2007 vizquom01 2007 1 SFN NL 145 513 54 126 18 thomafr04TOR2007 thomafr04 2007 1 TOR AL 155 531 63 147 30 rodriiv01DET2007 rodriiv01 2007 1 DET AL 129 502 50 141 31 griffke02CIN2007 griffke02 2007 1 CIN NL 144 528 78 146 24 delgaca01NYN2007 delgaca01 2007 1 NYN NL 139 538 71 139 30 biggicr01HOU2007 biggicr01 2007 1 HOU NL 141 517 68 130 31 X3b hr rbi sb cs bb so ibb hbp sh sf gidp gonzalu01ARI2006 2 15 73 0 1 69 58 10 7 0 6 14 vizquom01SFN2007 3 4 51 14 6 44 48 6 1 14 3 14 thomafr04TOR2007 0 26 95 0 0 81 94 3 7 0 5 14 rodriiv01DET2007 3 11 63 2 2 9 96 1 1 1 2 16 griffke02CIN2007 1 30 93 6 1 85 99 14 1 0 9 14 delgaca01NYN2007 0 24 87 4 0 52 118 8 11 0 6 12 biggicr01HOU2007 3 10 50 4 3 23 112 0 3 7 5 5
The indexing field ix
allows us to select subsets of rows and columns in an intuitive way:
baseball_newind.ix['gonzalu01ARI2006', ['h','X2b', 'X3b', 'hr']]
h 5 X2b 52 X3b 2 hr 15 Name: gonzalu01ARI2006, dtype: object
baseball_newind.ix[['gonzalu01ARI2006','finlest01SFN2006'], 5:8]
g ab r gonzalu01ARI2006 153 586 93 finlest01SFN2006 139 426 66
baseball_newind.ix[:'myersmi01NYA2006', 'hr']
womacto01CHN2006 1 schilcu01BOS2006 0 myersmi01NYA2006 0 Name: hr, dtype: int64
Similarly, the cross-section method xs
(not a field) extracts a single column or row by label and returns it as a Series
:
baseball_newind.xs('myersmi01NYA2006')
player myersmi01 year 2006 stint 1 team NYA lg AL g 62 ab 0 r 0 ... cs 0 bb 0 so 0 ibb 0 hbp 0 sh 0 sf 0 gidp 0 Name: myersmi01NYA2006, Length: 22, dtype: object
DataFrame
and Series
objects allow for several operations to take place either on a single object, or between two or more objects.
For example, we can perform arithmetic on the elements of two objects, such as combining baseball statistics across years:
hr2006 = baseball[baseball.year==2006].xs('hr', axis=1)
hr2006.index = baseball.player[baseball.year==2006]
hr2007 = baseball[baseball.year==2007].xs('hr', axis=1)
hr2007.index = baseball.player[baseball.year==2007]
hr2006 = pd.Series(baseball.hr[baseball.year==2006].values, index=baseball.player[baseball.year==2006])
hr2007 = pd.Series(baseball.hr[baseball.year==2007].values, index=baseball.player[baseball.year==2007])
hr_total = hr2006 + hr2007
hr_total
player alomasa02 NaN aloumo01 NaN ausmubr01 NaN benitar01 NaN benitar01 NaN biggicr01 NaN bondsba01 NaN cirilje01 NaN ... whiteri01 NaN whitero02 NaN wickmbo01 NaN wickmbo01 NaN williwo02 NaN witasja01 NaN womacto01 NaN zaungr01 NaN Length: 94, dtype: float64
Pandas' data alignment places NaN
values for labels that do not overlap in the two Series. In fact, there are only 6 players that occur in both years.
hr_total[hr_total.notnull()]
player finlest01 7 gonzalu01 30 johnsra05 0 myersmi01 0 schilcu01 0 seleaa01 0 dtype: float64
While we do want the operation to honor the data labels in this way, we probably do not want the missing values to be filled with NaN
. We can use the add
method to calculate player home run totals by using the fill_value
argument to insert a zero for home runs where labels do not overlap:
hr2007.add(hr2006, fill_value=0)
player alomasa02 0 aloumo01 13 ausmubr01 3 benitar01 0 benitar01 0 biggicr01 10 bondsba01 28 cirilje01 0 ... whiteri01 0 whitero02 4 wickmbo01 0 wickmbo01 0 williwo02 1 witasja01 0 womacto01 1 zaungr01 10 Length: 94, dtype: float64
Operations can also be broadcast between rows or columns.
For example, if we subtract the maximum number of home runs hit from the hr
column, we get how many fewer than the maximum were hit by each player:
baseball.hr - baseball.hr.max()
id 88641 -34 88643 -35 88645 -35 88649 -35 88650 -35 88652 -29 88653 -20 88662 -35 ... 89502 -33 89521 -7 89523 -25 89525 -35 89526 -35 89530 -32 89533 -22 89534 -35 Name: hr, Length: 100, dtype: int64
Or, looking at things row-wise, we can see how a particular player compares with the rest of the group with respect to important statistics
baseball.ix[89521]["player"]
'bondsba01'
stats = baseball[['h','X2b', 'X3b', 'hr']]
diff = stats - stats.xs(89521)
diff[:10]
h X2b X3b hr id 88641 -80 -13 0 -27 88643 -93 -14 0 -28 88645 -94 -14 0 -28 88649 -94 -14 0 -28 88650 -93 -14 0 -28 88652 11 7 12 -22 88653 65 38 2 -13 88662 -89 -13 0 -28 89177 -84 -11 0 -28 89178 -84 -14 0 -27
We can also apply functions to each column or row of a DataFrame
stats.apply(np.median)
h 8 X2b 1 X3b 0 hr 0 dtype: float64
stat_range = lambda x: x.max() - x.min()
stats.apply(stat_range)
h 159 X2b 52 X3b 12 hr 35 dtype: int64
Lets use apply to calculate a meaningful baseball statistics, slugging percentage:
$$SLG = \frac{1B + (2 \times 2B) + (3 \times 3B) + (4 \times HR)}{AB}$$And just for fun, we will format the resulting estimate.
slg = lambda x: (x['h']-x['X2b']-x['X3b']-x['hr'] + 2*x['X2b'] + 3*x['X3b'] + 4*x['hr'])/(x['ab']+1e-6)
baseball.apply(slg, axis=1).apply(lambda x: '%.3f' % x)
id 88641 0.360 88643 0.500 88645 0.000 88649 0.000 88650 0.167 88652 0.394 88653 0.444 88662 0.231 ... 89502 0.386 89521 0.565 89523 0.381 89525 0.000 89526 0.000 89530 0.324 89533 0.524 89534 0.182 Length: 100, dtype: object
Pandas objects include methods for re-ordering data.
baseball_newind.sort_index().head()
player year stint team lg g ab r h X2b \ alomasa02NYN2007 alomasa02 2007 1 NYN NL 8 22 1 3 1 aloumo01NYN2007 aloumo01 2007 1 NYN NL 87 328 51 112 19 ausmubr01HOU2007 ausmubr01 2007 1 HOU NL 117 349 38 82 16 benitar01FLO2007 benitar01 2007 2 FLO NL 34 0 0 0 0 benitar01SFN2007 benitar01 2007 1 SFN NL 19 0 0 0 0 X3b hr rbi sb cs bb so ibb hbp sh sf gidp alomasa02NYN2007 0 0 0 0 0 0 3 0 0 0 0 0 aloumo01NYN2007 1 13 49 3 0 27 30 5 2 0 3 13 ausmubr01HOU2007 3 3 25 6 1 37 74 3 6 4 1 11 benitar01FLO2007 0 0 0 0 0 0 0 0 0 0 0 0 benitar01SFN2007 0 0 0 0 0 0 0 0 0 0 0 0
baseball_newind.sort_index(ascending=False).head()
player year stint team lg g ab r h X2b X3b \ zaungr01TOR2007 zaungr01 2007 1 TOR AL 110 331 43 80 24 1 womacto01CHN2006 womacto01 2006 2 CHN NL 19 50 6 5 1 0 witasja01TBA2007 witasja01 2007 1 TBA AL 3 0 0 0 0 0 williwo02HOU2007 williwo02 2007 1 HOU NL 33 59 3 6 0 0 wickmbo01ATL2007 wickmbo01 2007 1 ATL NL 47 0 0 0 0 0 hr rbi sb cs bb so ibb hbp sh sf gidp zaungr01TOR2007 10 52 0 0 51 55 8 2 1 6 9 womacto01CHN2006 1 2 1 1 4 4 0 0 3 0 0 witasja01TBA2007 0 0 0 0 0 0 0 0 0 0 0 williwo02HOU2007 1 2 0 0 0 25 0 0 5 0 1 wickmbo01ATL2007 0 0 0 0 0 0 0 0 0 0 0
baseball_newind.sort_index(axis=1).head()
X2b X3b ab bb cs g gidp h hbp hr ibb lg \ womacto01CHN2006 1 0 50 4 1 19 0 5 0 1 0 NL schilcu01BOS2006 0 0 2 0 0 31 0 5 0 0 0 AL myersmi01NYA2006 0 0 0 0 0 62 0 5 0 0 0 AL helliri01MIL2006 0 0 3 0 0 20 0 5 0 0 0 NL johnsra05NYA2006 0 0 6 0 0 33 0 5 0 0 0 AL player r rbi sb sf sh so stint team year womacto01CHN2006 womacto01 6 2 1 0 3 4 2 CHN 2006 schilcu01BOS2006 schilcu01 0 0 0 0 0 1 1 BOS 2006 myersmi01NYA2006 myersmi01 0 0 0 0 0 0 1 NYA 2006 helliri01MIL2006 helliri01 0 0 0 0 0 2 1 MIL 2006 johnsra05NYA2006 johnsra05 0 0 0 0 0 4 1 NYA 2006
We can also use order
to sort a Series
by value, rather than by label.
baseball.hr.order(ascending=False)
id 89360 35 89462 30 89521 28 89361 26 89378 25 89489 24 89371 21 89374 21 ... 89465 0 89372 0 89467 0 89370 0 89367 0 89469 0 89365 0 89534 0 Name: hr, Length: 100, dtype: int64
For a DataFrame
, we can sort according to the values of one or more columns using the by
argument of sort_index
:
baseball[['player','sb','cs']].sort_index(ascending=[False,True], by=['sb', 'cs']).head(10)
player sb cs id 89378 sheffga01 22 5 89430 loftoke01 21 4 89347 vizquom01 14 6 89463 greensh01 11 1 88652 finlest01 7 0 89462 griffke02 6 1 89530 ausmubr01 6 1 89466 gonzalu01 6 2 89521 bondsba01 5 0 89438 kleskry01 5 1
Ranking does not re-arrange data, but instead returns an index that ranks each value relative to others in the Series.
baseball.hr.rank()
id 88641 62.5 88643 29.0 88645 29.0 88649 29.0 88650 29.0 88652 76.0 88653 89.5 88662 29.0 ... 89502 69.0 89521 98.0 89523 83.5 89525 29.0 89526 29.0 89530 71.5 89533 88.0 89534 29.0 Name: hr, Length: 100, dtype: float64
Ties are assigned the mean value of the tied ranks, which may result in decimal values.
pd.Series([100,100]).rank()
0 1.5 1 1.5 dtype: float64
Alternatively, you can break ties via one of several methods, such as by the order in which they occur in the dataset:
baseball.hr.rank(method='first')
id 88641 58 88643 1 88645 2 88649 3 88650 4 88652 75 88653 89 88662 5 ... 89502 70 89521 98 89523 85 89525 55 89526 56 89530 72 89533 88 89534 57 Name: hr, Length: 100, dtype: float64
Calling the DataFrame
's rank
method results in the ranks of all columns:
baseball.rank(ascending=False).head()
player year stint team lg g ab r h X2b X3b \ id 88641 2.0 96.5 7 82.0 31.5 70.0 47.5 40.5 39.0 50.5 63.5 88643 37.5 96.5 57 88.0 81.5 55.5 73.0 81.0 63.5 78.0 63.5 88645 47.5 96.5 57 40.5 81.5 36.0 91.0 81.0 84.5 78.0 63.5 88649 66.0 96.5 57 47.0 31.5 67.5 69.0 81.0 84.5 78.0 63.5 88650 61.5 96.5 57 40.5 81.5 51.0 64.5 81.0 63.5 78.0 63.5 hr rbi sb cs bb so ibb hbp sh sf gidp id 88641 38.5 51.0 24.5 17.5 44.5 59 66 65.5 16.0 70 76.5 88643 72.0 78.5 63.5 62.5 79.0 73 66 65.5 67.5 70 76.5 88645 72.0 78.5 63.5 62.5 79.0 89 66 65.5 67.5 70 76.5 88649 72.0 78.5 63.5 62.5 79.0 67 66 65.5 67.5 70 76.5 88650 72.0 78.5 63.5 62.5 79.0 59 66 65.5 67.5 70 76.5
baseball[['r','h','hr']].rank(ascending=False).head()
r h hr id 88641 40.5 39.0 38.5 88643 81.0 63.5 72.0 88645 81.0 84.5 72.0 88649 81.0 84.5 72.0 88650 81.0 63.5 72.0
Calculate on base percentage for each player, and return the ordered series of estimates.
$$OBP = \frac{H + BB + HBP}{AB + BB + HBP + SF}$$# Write your answer here
In the baseball example, I was forced to combine 3 fields to obtain a unique index that was not simply an integer value. A more elegant way to have done this would be to create a hierarchical index from the three fields.
baseball_h = baseball.set_index(['year', 'team', 'player'])
baseball_h.head(10)
stint lg g ab r h X2b X3b hr rbi sb cs \ year team player 2006 CHN womacto01 2 NL 19 50 6 14 1 0 1 2 1 1 BOS schilcu01 1 AL 31 2 0 1 0 0 0 0 0 0 NYA myersmi01 1 AL 62 0 0 0 0 0 0 0 0 0 MIL helliri01 1 NL 20 3 0 0 0 0 0 0 0 0 NYA johnsra05 1 AL 33 6 0 1 0 0 0 0 0 0 SFN finlest01 1 NL 139 426 66 105 21 12 6 40 7 0 ARI gonzalu01 1 NL 153 586 93 159 52 2 15 73 0 1 LAN seleaa01 1 NL 28 26 2 5 1 0 0 0 0 0 2007 ATL francju01 2 NL 15 40 1 10 3 0 0 8 0 0 NYN francju01 1 NL 40 50 7 10 0 0 1 8 2 1 bb so ibb hbp sh sf gidp year team player 2006 CHN womacto01 4 4 0 0 3 0 0 BOS schilcu01 0 1 0 0 0 0 0 NYA myersmi01 0 0 0 0 0 0 0 MIL helliri01 0 2 0 0 0 0 0 NYA johnsra05 0 4 0 0 0 0 0 SFN finlest01 46 55 2 2 3 4 6 ARI gonzalu01 69 58 10 7 0 6 14 LAN seleaa01 1 7 0 0 6 0 1 2007 ATL francju01 4 10 1 0 0 1 1 NYN francju01 10 13 0 0 0 1 1
This index is a MultiIndex
object that consists of a sequence of tuples, the elements of which is some combination of the three columns used to create the index. Where there are multiple repeated values, Pandas does not print the repeats, making it easy to identify groups of values.
baseball_h.index[:10]
MultiIndex(levels=[[2006, 2007], [u'ARI', u'ATL', u'BAL', u'BOS', u'CHA', u'CHN', u'CIN', u'CLE', u'COL', u'DET', u'FLO', u'HOU', u'KCA', u'LAA', u'LAN', u'MIL', u'MIN', u'NYA', u'NYN', u'OAK', u'PHI', u'SDN', u'SFN', u'SLN', u'TBA', u'TEX', u'TOR'], [u'alomasa02', u'aloumo01', u'ausmubr01', u'benitar01', u'biggicr01', u'bondsba01', u'cirilje01', u'claytro01', u'clemero02', u'coninje01', u'cormirh01', u'delgaca01', u'easleda01', u'edmonji01', u'embreal01', u'finlest01', u'floydcl01', u'francju01', u'glavito02', u'gomezch02', u'gonzalu01', u'gordoto01', u'graffto01', u'greensh01', u'griffke02', u'guarded01', u'helliri01', u'hernaro01', u'hoffmtr01', u'johnsra05', u'jonesto02', u'kentje01', u'kleskry01', u'loaizes01', u'loftoke01', u'mabryjo01', u'maddugr01', u'martipe02', u'mesajo01', u'moyerja01', u'mussimi01', u'myersmi01', u'oliveda02', u'parkch01', u'perezne01', u'piazzmi01', u'ramirma02', u'rodriiv01', u'rogerke01', u'sandere02', u'schilcu01', u'schmija01', u'seaneru01', u'seleaa01', u'sheffga01', u'smoltjo01', u'sosasa01', u'sprinru01', u'stairma01', u'stantmi02', u'stinnke01', u'suppaje01', u'sweenma01', u'tavarju01', u'thomafr04', u'thomeji01', u'timlimi01', u'trachst01', u'valenjo03', u'villoro01', u'vizquom01', u'wakefti01', u'walketo04', u'weathda01', u'wellsda01', u'whiteri01', u'whitero02', u'wickmbo01', u'williwo02', u'witasja01', u'womacto01', u'zaungr01']], labels=[[0, 0, 0, 0, 0, 0, 0, 0, 1, 1], [5, 3, 17, 15, 17, 22, 0, 14, 1, 18], [80, 50, 41, 26, 29, 15, 20, 53, 17, 17]], names=[u'year', u'team', u'player'])
baseball_h.index.is_unique
True
baseball_h.ix[(2007, 'ATL', 'francju01')]
stint 2 lg NL g 15 ab 40 r 1 h 10 X2b 3 X3b 0 hr 0 rbi 8 sb 0 cs 0 bb 4 so 10 ibb 1 hbp 0 sh 0 sf 1 gidp 1 Name: (2007, ATL, francju01), dtype: object
Recall earlier we imported some microbiome data using two index columns. This created a 2-level hierarchical index:
mb = pd.read_csv("data/microbiome.csv", index_col=['Taxon','Patient'])
mb.head(10)
Tissue Stool Taxon Patient Firmicutes 1 632 305 2 136 4182 3 1174 703 4 408 3946 5 831 8605 6 693 50 7 718 717 8 173 33 9 228 80 10 162 3196
mb.index
MultiIndex(levels=[[u'Actinobacteria', u'Bacteroidetes', u'Firmicutes', u'Other', u'Proteobacteria'], [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]], labels=[[2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3], [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14]], names=[u'Taxon', u'Patient'])
With a hierachical index, we can select subsets of the data based on a partial index:
mb.ix['Proteobacteria']
Tissue Stool Patient 1 1638 3886 2 2469 1821 3 839 661 4 4414 18 5 12044 83 6 2310 12 7 3053 547 8 395 2174 9 2651 767 10 1195 76 11 6857 795 12 483 666 13 2950 3994 14 1541 816 15 1307 53
Hierarchical indices can be created on either or both axes. Here is a trivial example:
frame = pd.DataFrame(np.arange(12).reshape(( 4, 3)),
index =[['a', 'a', 'b', 'b'], [1, 2, 1, 2]],
columns =[['Ohio', 'Ohio', 'Colorado'], ['Green', 'Red', 'Green']])
frame
Ohio Colorado Green Red Green a 1 0 1 2 2 3 4 5 b 1 6 7 8 2 9 10 11
If you want to get fancy, both the row and column indices themselves can be given names:
frame.index.names = ['key1', 'key2']
frame.columns.names = ['state', 'color']
frame
state Ohio Colorado color Green Red Green key1 key2 a 1 0 1 2 2 3 4 5 b 1 6 7 8 2 9 10 11
With this, we can do all sorts of custom indexing:
frame.ix['a']['Ohio']
color Green Red key2 1 0 1 2 3 4
frame.ix['b', 2]['Colorado']
color Green 11 Name: (b, 2), dtype: int64
Additionally, the order of the set of indices in a hierarchical MultiIndex
can be changed by swapping them pairwise:
mb.swaplevel('Patient', 'Taxon').head()
Tissue Stool Patient Taxon 1 Firmicutes 632 305 2 Firmicutes 136 4182 3 Firmicutes 1174 703 4 Firmicutes 408 3946 5 Firmicutes 831 8605
Data can also be sorted by any index level, using sortlevel
:
mb.sortlevel('Patient', ascending=False).head()
Tissue Stool Taxon Patient Proteobacteria 15 1307 53 Other 15 305 32 Firmicutes 15 281 2377 Bacteroidetes 15 102 33 Actinobacteria 15 310 204
The occurence of missing data is so prevalent that it pays to use tools like Pandas, which seamlessly integrates missing data handling so that it can be dealt with easily, and in the manner required by the analysis at hand.
Missing data are represented in Series
and DataFrame
objects by the NaN
floating point value. However, None
is also treated as missing, since it is commonly used as such in other contexts (e.g. NumPy).
foo = pd.Series([np.nan, -3, None, 'foobar'])
foo
0 NaN 1 -3 2 None 3 foobar dtype: object
foo.isnull()
0 True 1 False 2 True 3 False dtype: bool
Missing values may be dropped or indexed out:
bacteria2
phylum Firmicutes NaN Proteobacteria 632 Actinobacteria 1638 Bacteroidetes 569 dtype: float64
bacteria2.dropna()
phylum Proteobacteria 632 Actinobacteria 1638 Bacteroidetes 569 dtype: float64
bacteria2[bacteria2.notnull()]
phylum Proteobacteria 632 Actinobacteria 1638 Bacteroidetes 569 dtype: float64
By default, dropna
drops entire rows in which one or more values are missing.
data
patient phylum value year treatment 0 1 Firmicutes 632 2013 0 1 1 Proteobacteria 1638 2013 0 2 1 Actinobacteria 569 2013 0 3 1 Bacteroidetes 14 2013 0 4 2 Firmicutes 433 2013 1 5 2 Proteobacteria 0 2013 1 6 2 Actinobacteria 754 2013 NaN 7 2 Bacteroidetes 555 2013 NaN
data.dropna()
patient phylum value year treatment 0 1 Firmicutes 632 2013 0 1 1 Proteobacteria 1638 2013 0 2 1 Actinobacteria 569 2013 0 3 1 Bacteroidetes 14 2013 0 4 2 Firmicutes 433 2013 1 5 2 Proteobacteria 0 2013 1
This can be overridden by passing the how='all'
argument, which only drops a row when every field is a missing value.
data.dropna(how='all')
patient phylum value year treatment 0 1 Firmicutes 632 2013 0 1 1 Proteobacteria 1638 2013 0 2 1 Actinobacteria 569 2013 0 3 1 Bacteroidetes 14 2013 0 4 2 Firmicutes 433 2013 1 5 2 Proteobacteria 0 2013 1 6 2 Actinobacteria 754 2013 NaN 7 2 Bacteroidetes 555 2013 NaN
This can be customized further by specifying how many values need to be present before a row is dropped via the thresh
argument.
data.ix[7, 'year'] = np.nan
data
patient phylum value year treatment 0 1 Firmicutes 632 2013 0 1 1 Proteobacteria 1638 2013 0 2 1 Actinobacteria 569 2013 0 3 1 Bacteroidetes 14 2013 0 4 2 Firmicutes 433 2013 1 5 2 Proteobacteria 0 2013 1 6 2 Actinobacteria 754 2013 NaN 7 2 Bacteroidetes 555 NaN NaN
data.dropna(thresh=4)
patient phylum value year treatment 0 1 Firmicutes 632 2013 0 1 1 Proteobacteria 1638 2013 0 2 1 Actinobacteria 569 2013 0 3 1 Bacteroidetes 14 2013 0 4 2 Firmicutes 433 2013 1 5 2 Proteobacteria 0 2013 1 6 2 Actinobacteria 754 2013 NaN
This is typically used in time series applications, where there are repeated measurements that are incomplete for some subjects.
If we want to drop missing values column-wise instead of row-wise, we use axis=1
.
data.dropna(axis=1)
patient phylum value 0 1 Firmicutes 632 1 1 Proteobacteria 1638 2 1 Actinobacteria 569 3 1 Bacteroidetes 14 4 2 Firmicutes 433 5 2 Proteobacteria 0 6 2 Actinobacteria 754 7 2 Bacteroidetes 555
Rather than omitting missing data from an analysis, in some cases it may be suitable to fill the missing value in, either with a default value (such as zero) or a value that is either imputed or carried forward/backward from similar data points. We can do this programmatically in Pandas with the fillna
argument.
bacteria2.fillna(0)
phylum Firmicutes 0 Proteobacteria 632 Actinobacteria 1638 Bacteroidetes 569 dtype: float64
data.fillna({'year': 2013, 'treatment':2})
patient phylum value year treatment 0 1 Firmicutes 632 2013 0 1 1 Proteobacteria 1638 2013 0 2 1 Actinobacteria 569 2013 0 3 1 Bacteroidetes 14 2013 0 4 2 Firmicutes 433 2013 1 5 2 Proteobacteria 0 2013 1 6 2 Actinobacteria 754 2013 2 7 2 Bacteroidetes 555 2013 2
Notice that fillna
by default returns a new object with the desired filling behavior, rather than changing the Series
or DataFrame
in place (in general, we like to do this, by the way!).
data
patient phylum value year treatment 0 1 Firmicutes 632 2013 0 1 1 Proteobacteria 1638 2013 0 2 1 Actinobacteria 569 2013 0 3 1 Bacteroidetes 14 2013 0 4 2 Firmicutes 433 2013 1 5 2 Proteobacteria 0 2013 1 6 2 Actinobacteria 754 2013 NaN 7 2 Bacteroidetes 555 NaN NaN
We can alter values in-place using inplace=True
.
_ = data.year.fillna(2013, inplace=True)
data
patient phylum value year treatment 0 1 Firmicutes 632 2013 0 1 1 Proteobacteria 1638 2013 0 2 1 Actinobacteria 569 2013 0 3 1 Bacteroidetes 14 2013 0 4 2 Firmicutes 433 2013 1 5 2 Proteobacteria 0 2013 1 6 2 Actinobacteria 754 2013 NaN 7 2 Bacteroidetes 555 2013 NaN
Missing values can also be interpolated, using any one of a variety of methods:
bacteria2.fillna(method='bfill')
phylum Firmicutes 632 Proteobacteria 632 Actinobacteria 1638 Bacteroidetes 569 dtype: float64
bacteria2.fillna(bacteria2.mean())
phylum Firmicutes 946.333333 Proteobacteria 632.000000 Actinobacteria 1638.000000 Bacteroidetes 569.000000 dtype: float64
We often wish to summarize data in Series
or DataFrame
objects, so that they can more easily be understood or compared with similar data. The NumPy package contains several functions that are useful here, but several summarization or reduction methods are built into Pandas data structures.
baseball.sum()
player womacto01schilcu01myersmi01helliri01johnsra05f... year 200692 stint 113 team CHNBOSNYAMILNYASFNARILANATLNYNTORTBAHOUARIATLM... lg NLALALNLALNLNLNLNLNLALALNLNLNLALNLNLNLNLALALNL... g 5238 ab 13654 r 1869 ... cs 46 bb 1549 so 2408 ibb 177 hbp 112 sh 138 sf 120 gidp 354 Length: 22, dtype: object
Clearly, sum
is more meaningful for some columns than others. For methods like mean
for which application to string variables is not just meaningless, but impossible, these columns are automatically exculded:
baseball.mean()
year 2006.92 stint 1.13 g 52.38 ab 136.54 r 18.69 h 35.82 X2b 7.39 X3b 0.55 hr 4.37 rbi 18.47 sb 1.38 cs 0.46 bb 15.49 so 24.08 ibb 1.77 hbp 1.12 sh 1.38 sf 1.20 gidp 3.54 dtype: float64
The important difference between NumPy's functions and Pandas' methods is that the latter have built-in support for handling missing data.
bacteria2
phylum Firmicutes NaN Proteobacteria 632 Actinobacteria 1638 Bacteroidetes 569 dtype: float64
bacteria2.mean()
946.33333333333337
Sometimes we may not want to ignore missing values, and allow the nan
to propagate.
bacteria2.mean(skipna=False)
nan
Passing axis=1
will summarize over rows instead of columns, which only makes sense in certain situations.
extra_bases = baseball[['X2b','X3b','hr']].sum(axis=1)
extra_bases.order(ascending=False)
id 88653 69 89439 57 89361 56 89462 55 89396 54 89489 54 89360 54 89371 50 ... 89445 0 89388 0 89359 0 89355 0 89354 0 89480 0 89348 0 89420 0 Length: 100, dtype: int64
A useful summarization that gives a quick snapshot of multiple statistics for a Series
or DataFrame
is describe
:
baseball.describe()
year stint g ab r h \ count 100.00000 100.000000 100.000000 100.000000 100.00000 100.000000 mean 2006.92000 1.130000 52.380000 136.540000 18.69000 35.820000 std 0.27266 0.337998 48.031299 181.936853 27.77496 50.221807 min 2006.00000 1.000000 1.000000 0.000000 0.00000 0.000000 25% 2007.00000 1.000000 9.500000 2.000000 0.00000 0.000000 50% 2007.00000 1.000000 33.000000 40.500000 2.00000 8.000000 75% 2007.00000 1.000000 83.250000 243.750000 33.25000 62.750000 max 2007.00000 2.000000 155.000000 586.000000 107.00000 159.000000 X2b X3b hr rbi sb cs \ count 100.000000 100.000000 100.000000 100.00000 100.000000 100.000000 mean 7.390000 0.550000 4.370000 18.47000 1.380000 0.460000 std 11.117277 1.445124 7.975537 28.34793 3.694878 1.067613 min 0.000000 0.000000 0.000000 0.00000 0.000000 0.000000 25% 0.000000 0.000000 0.000000 0.00000 0.000000 0.000000 50% 1.000000 0.000000 0.000000 2.00000 0.000000 0.000000 75% 11.750000 1.000000 6.000000 27.00000 1.000000 0.000000 max 52.000000 12.000000 35.000000 96.00000 22.000000 6.000000 bb so ibb hbp sh sf \ count 100.000000 100.000000 100.000000 100.00000 100.000000 100.000000 mean 15.490000 24.080000 1.770000 1.12000 1.380000 1.200000 std 25.812649 32.804496 5.042957 2.23055 2.919042 2.035046 min 0.000000 0.000000 0.000000 0.00000 0.000000 0.000000 25% 0.000000 1.000000 0.000000 0.00000 0.000000 0.000000 50% 1.000000 7.000000 0.000000 0.00000 0.000000 0.000000 75% 19.250000 37.250000 1.250000 1.00000 1.000000 2.000000 max 132.000000 134.000000 43.000000 11.00000 14.000000 9.000000 gidp count 100.000000 mean 3.540000 std 5.201826 min 0.000000 25% 0.000000 50% 1.000000 75% 6.000000 max 21.000000
describe
can detect non-numeric data and sometimes yield useful information about it.
baseball.player.describe()
count 100 unique 82 top coninje01 freq 2 dtype: object
We can also calculate summary statistics across multiple columns, for example, correlation and covariance.
$$cov(x,y) = \sum_i (x_i - \bar{x})(y_i - \bar{y})$$baseball.hr.cov(baseball.X2b)
69.076464646464629
baseball.hr.corr(baseball.X2b)
0.77906151825397507
baseball.ab.corr(baseball.h)
0.99421740362723787
baseball.corr()
year stint g ab r h X2b \ year 1.000000 0.004384 -0.050874 -0.001360 -0.023315 0.001151 -0.052917 stint 0.004384 1.000000 -0.257552 -0.216333 -0.209781 -0.206878 -0.196423 g -0.050874 -0.257552 1.000000 0.935910 0.910262 0.929292 0.885847 ab -0.001360 -0.216333 0.935910 1.000000 0.965609 0.994217 0.952249 r -0.023315 -0.209781 0.910262 0.965609 1.000000 0.970560 0.923508 h 0.001151 -0.206878 0.929292 0.994217 0.970560 1.000000 0.957275 X2b -0.052917 -0.196423 0.885847 0.952249 0.923508 0.957275 1.000000 X3b -0.246099 -0.085821 0.518663 0.535986 0.500807 0.514245 0.493267 hr 0.060199 -0.209124 0.802014 0.843308 0.890060 0.855163 0.779062 rbi 0.042812 -0.205688 0.891563 0.947911 0.941483 0.952320 0.901751 sb 0.030480 -0.120837 0.492362 0.533536 0.596343 0.530018 0.413655 cs 0.058296 -0.055425 0.520923 0.577192 0.576454 0.571629 0.477487 bb 0.005626 -0.190301 0.828572 0.850803 0.915010 0.853384 0.780012 so 0.069610 -0.214121 0.866499 0.923926 0.879375 0.906966 0.862149 ibb 0.015868 -0.118580 0.514423 0.506398 0.588882 0.513009 0.453301 hbp -0.000664 -0.195074 0.730161 0.767210 0.806523 0.767449 0.738226 sh -0.012184 -0.091527 0.079361 0.094537 -0.001273 0.045533 0.005659 sf -0.007282 -0.155662 0.767543 0.840361 0.839592 0.839737 0.819361 gidp 0.052131 -0.224173 0.863041 0.926632 0.894724 0.935525 0.906860 X3b hr rbi sb cs bb so \ year -0.246099 0.060199 0.042812 0.030480 0.058296 0.005626 0.069610 stint -0.085821 -0.209124 -0.205688 -0.120837 -0.055425 -0.190301 -0.214121 g 0.518663 0.802014 0.891563 0.492362 0.520923 0.828572 0.866499 ab 0.535986 0.843308 0.947911 0.533536 0.577192 0.850803 0.923926 r 0.500807 0.890060 0.941483 0.596343 0.576454 0.915010 0.879375 h 0.514245 0.855163 0.952320 0.530018 0.571629 0.853384 0.906966 X2b 0.493267 0.779062 0.901751 0.413655 0.477487 0.780012 0.862149 X3b 1.000000 0.210028 0.369890 0.450421 0.384312 0.350682 0.408800 hr 0.210028 1.000000 0.948787 0.364346 0.345187 0.916774 0.865929 rbi 0.369890 0.948787 1.000000 0.394633 0.435011 0.893945 0.929410 sb 0.450421 0.364346 0.394633 1.000000 0.743921 0.491351 0.365841 cs 0.384312 0.345187 0.435011 0.743921 1.000000 0.425352 0.426658 bb 0.350682 0.916774 0.893945 0.491351 0.425352 1.000000 0.795751 so 0.408800 0.865929 0.929410 0.365841 0.426658 0.795751 1.000000 ibb 0.090993 0.673691 0.582982 0.209110 0.106152 0.792057 0.476613 hbp 0.217474 0.767411 0.780899 0.413570 0.337129 0.742118 0.742547 sh 0.187012 -0.145374 -0.054670 0.171910 0.293397 -0.044992 0.073624 sf 0.394987 0.782038 0.855260 0.412947 0.422146 0.760932 0.782314 gidp 0.411577 0.798350 0.906908 0.431198 0.456820 0.823631 0.846629 ibb hbp sh sf gidp year 0.015868 -0.000664 -0.012184 -0.007282 0.052131 stint -0.118580 -0.195074 -0.091527 -0.155662 -0.224173 g 0.514423 0.730161 0.079361 0.767543 0.863041 ab 0.506398 0.767210 0.094537 0.840361 0.926632 r 0.588882 0.806523 -0.001273 0.839592 0.894724 h 0.513009 0.767449 0.045533 0.839737 0.935525 X2b 0.453301 0.738226 0.005659 0.819361 0.906860 X3b 0.090993 0.217474 0.187012 0.394987 0.411577 hr 0.673691 0.767411 -0.145374 0.782038 0.798350 rbi 0.582982 0.780899 -0.054670 0.855260 0.906908 sb 0.209110 0.413570 0.171910 0.412947 0.431198 cs 0.106152 0.337129 0.293397 0.422146 0.456820 bb 0.792057 0.742118 -0.044992 0.760932 0.823631 so 0.476613 0.742547 0.073624 0.782314 0.846629 ibb 1.000000 0.431714 -0.075658 0.451377 0.572355 hbp 0.431714 1.000000 -0.104810 0.648882 0.700380 sh -0.075658 -0.104810 1.000000 -0.002721 0.028924 sf 0.451377 0.648882 -0.002721 1.000000 0.785489 gidp 0.572355 0.700380 0.028924 0.785489 1.000000
If we have a DataFrame
with a hierarchical index (or indices), summary statistics can be applied with respect to any of the index levels:
mb.head()
Tissue Stool Taxon Patient Firmicutes 1 632 305 2 136 4182 3 1174 703 4 408 3946 5 831 8605
mb.sum(level='Taxon')
Tissue Stool Taxon Actinobacteria 6736 2263 Bacteroidetes 8995 4656 Firmicutes 10266 30477 Other 2982 519 Proteobacteria 44146 16369
As well as being able to read several data input formats, Pandas can also export data to a variety of storage formats. We will bring your attention to just a couple of these.
mb.to_csv("mb.csv")
The to_csv
method writes a DataFrame
to a comma-separated values (csv) file. You can specify custom delimiters (via sep
argument), how missing values are written (via na_rep
argument), whether the index is writen (via index
argument), whether the header is included (via header
argument), among other options.
An efficient way of storing data to disk is in binary format. Pandas supports this using Python’s built-in pickle serialization.
baseball.to_pickle("baseball_pickle")
The complement to to_pickle
is the read_pickle
function, which restores the pickle to a DataFrame
or Series
:
pd.read_pickle("baseball_pickle")
player year stint team lg g ab r h X2b X3b hr rbi \ id 88641 womacto01 2006 2 CHN NL 19 50 6 14 1 0 1 2 88643 schilcu01 2006 1 BOS AL 31 2 0 1 0 0 0 0 88645 myersmi01 2006 1 NYA AL 62 0 0 0 0 0 0 0 88649 helliri01 2006 1 MIL NL 20 3 0 0 0 0 0 0 88650 johnsra05 2006 1 NYA AL 33 6 0 1 0 0 0 0 88652 finlest01 2006 1 SFN NL 139 426 66 105 21 12 6 40 88653 gonzalu01 2006 1 ARI NL 153 586 93 159 52 2 15 73 88662 seleaa01 2006 1 LAN NL 28 26 2 5 1 0 0 0 89177 francju01 2007 2 ATL NL 15 40 1 10 3 0 0 8 89178 francju01 2007 1 NYN NL 40 50 7 10 0 0 1 8 ... ... ... ... ... .. ... ... .. ... ... ... .. ... 89499 claytro01 2007 1 TOR AL 69 189 23 48 14 0 1 12 89501 cirilje01 2007 2 ARI NL 28 40 6 8 4 0 0 6 89502 cirilje01 2007 1 MIN AL 50 153 18 40 9 2 2 21 89521 bondsba01 2007 1 SFN NL 126 340 75 94 14 0 28 66 89523 biggicr01 2007 1 HOU NL 141 517 68 130 31 3 10 50 89525 benitar01 2007 2 FLO NL 34 0 0 0 0 0 0 0 89526 benitar01 2007 1 SFN NL 19 0 0 0 0 0 0 0 89530 ausmubr01 2007 1 HOU NL 117 349 38 82 16 3 3 25 89533 aloumo01 2007 1 NYN NL 87 328 51 112 19 1 13 49 89534 alomasa02 2007 1 NYN NL 8 22 1 3 1 0 0 0 sb cs bb so ibb hbp sh sf gidp id 88641 1 1 4 4 0 0 3 0 0 88643 0 0 0 1 0 0 0 0 0 88645 0 0 0 0 0 0 0 0 0 88649 0 0 0 2 0 0 0 0 0 88650 0 0 0 4 0 0 0 0 0 88652 7 0 46 55 2 2 3 4 6 88653 0 1 69 58 10 7 0 6 14 88662 0 0 1 7 0 0 6 0 1 89177 0 0 4 10 1 0 0 1 1 89178 2 1 10 13 0 0 0 1 1 ... .. .. ... ... ... ... .. .. ... 89499 2 1 14 50 0 1 3 3 8 89501 0 0 4 6 0 0 0 0 1 89502 2 0 15 13 0 1 3 2 9 89521 5 0 132 54 43 3 0 2 13 89523 4 3 23 112 0 3 7 5 5 89525 0 0 0 0 0 0 0 0 0 89526 0 0 0 0 0 0 0 0 0 89530 6 1 37 74 3 6 4 1 11 89533 3 0 27 30 5 2 0 3 13 89534 0 0 0 3 0 0 0 0 0 [100 rows x 22 columns]
As Wes warns in his book, it is recommended that binary storage of data via pickle only be used as a temporary storage format, in situations where speed is relevant. This is because there is no guarantee that the pickle format will not change with future versions of Python.